package com.surfilter.sqlopera.opera;

import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * MysqlUtils
 * Dis:mysql工具，
 */
public class MysqlUtils {
	private static Logger logger = LoggerFactory.getLogger(MysqlUtils.class);

	private static Connection con = null;
	private static PreparedStatement pstmt = null;
	private static ResultSet rs = null;

	/**
	 * 外汇入疑似网站（批量）
	 * @param beanList
     */
	public static void writeYsFinanceType(List<Map> beanList) {
		String insertSql = "insert into yswz (wzmc,sywzwwlj,fxfs,fxsj,dlcyz,ym,sfjrwz) values(?,?,?,?,?,?,2)";
		// 加载驱动
		con = SQLHelper.getConnection();
		try {
            pstmt = con.prepareStatement(insertSql);
            for (Map temp : beanList) {
                String wzmc = (String) temp.get("wzmc");
                String sywzwwlj = (String) temp.get("sywzwwlj");
                Integer fxfs = (Integer) temp.get("fxfs");
                String fxsj = (String) temp.get("fxsj");
                Integer dlcyz = (Integer) temp.get("dlcyz");
                String ym = (String) temp.get("ym");
                Object[] obj = new Object[6];
                obj[0] = wzmc;
                obj[1] = sywzwwlj;
                obj[2] = fxfs;
                obj[3] = fxsj;
                obj[4] = dlcyz;
                obj[5] = ym;
                // 声明一条准备的sql语句
                // 为Object对象一一赋值
                for (int i = 0; i < obj.length; i++) {
                    pstmt.setObject(i + 1, obj[i]);
                    // 批处理sql语句
                }
                pstmt.addBatch();
            }
            pstmt.executeBatch();
			logger.info("批量插入执行完成,共计{}条",beanList.size());
        } catch (Exception e) {
			logger.info("批量插入数据库报错,{}",e.getMessage());
            e.printStackTrace();
        } finally {
			SQLHelper.close(pstmt);
			SQLHelper.close(con);
        }
	}

	/**
	 * 根据疑似网站和确认金融网站去重
	 * @param inputList
	 * @return
     */
	public static List<Map> getDistinctInfoList(List<Map> inputList){
		String yswzsql = "select yswzid from yswz where ym ='%s' ";
		String qrwzsql = "select wzid from qrjrwz where ym ='%s' ";
		try {
			Iterator<Map> iterator = inputList.iterator();
			while(iterator.hasNext()){
				Map temp = iterator.next();
				String ym = (String) temp.get("ym");
				String yswzssql = String.format(yswzsql, ym);
				String qrwzssql = String.format(qrwzsql, ym);
				if(existMap("YSWZ",yswzssql,temp)){
					iterator.remove();
					continue;
				}
				if(existMap("QRJRWZ",qrwzssql,temp)){
					iterator.remove();
				}
			}
		}catch (Exception e){
			logger.error("去重出错，{}",e.getMessage());
			e.printStackTrace();
		}finally {
			SQLHelper.close(rs);
			SQLHelper.close(pstmt);
			SQLHelper.close(con);
		}
		return inputList;
	}

	/**
	 * 网站存在验证
	 * @param tableName
	 * @param sql
	 * @param map
	 * @return
	 * @throws SQLException
     */
	public static boolean existMap(String tableName, String sql, Map map) throws SQLException {
		con = SQLHelper.getConnection();
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			logger.info("{}网站存在，域名为：{}，跳过",tableName,map.get("ym"));
			return true;
		}else{
			return false;
		}
	}

	/**
	 * 从固定位置读取文件返回map
	 * @param filePath
	 * @return
     */
	public static List<Map> getInfoList(String filePath){
		File file = new File(filePath);
		List<Map> result = new ArrayList<>();
		try {
			List<String> list = FileUtils.readLines(file,"utf-8");
			for(String str:list){
				Map map = new HashMap();
				String[] splitStr = str.split(",");
				map.put("sywzwwlj",splitStr[1]);
				map.put("fxfs",6);
				map.put("fxsj","2017-11-24");
				map.put("dlcyz",501);
				map.put("ym",splitStr[0]);
				map.put("wzmc",splitStr[2]);
				result.add(map);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static void main(String[] args) {
		List<Map> map = getInfoList("file/外汇汇总——20171101.csv");
		writeYsFinanceType(getDistinctInfoList(map));
	}
	
}
